﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using ES_Project.Model;
using System.Web.Configuration;
using System.Data;
namespace ES_Project.Pages
{
    public partial class WebForm2 : System.Web.UI.Page
    {
       

        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand(); 

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                clsRegDetailId.Text = Request.QueryString["clsRegDetailId"];
                if (!clsRegDetailId.Text.Equals(""))
                {
                    clsRegDetailId.Text = Request.QueryString["clsRegDetailId"];
                    CMD_INSERT.Enabled = false;
                    CMD_EDIT.Enabled = true;
                    Editing();
                }
                else
                {
                    CMD_INSERT.Enabled = true;
                    CMD_EDIT.Enabled = false;
                    BindData();
                }
            }
                //BindData();
                //CMD_INSERT.Enabled = true;
                //CMD_EDIT.Enabled = false;
            
        }
        protected void CMD_ClsRegCode_Click(object sender, EventArgs e)
        {

            using (essystemEntities context = new essystemEntities())
            {
                List<cls_register_mast> crm2 = context.cls_register_mast.Where("it.[cls_reg_code]= '" + clsRegCode.Text + "' ").ToList();
                if (crm2.Count() == 1)
                {
                    cls_register_mast em = context.cls_register_mast.Where("it.[cls_reg_code]='" + clsRegCode.Text + "'").First();
                    if (em.cls_reg_code != null)
                    {
                        clsName.Text = em.cls_name;
                        clsRegId.Text = Convert.ToString(em.cls_reg_id);

                        RuningNumber(clsRegCode.Text);

                    }
                }
            }
        }

        private void RuningNumber(string p)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;


            strSQL = "SELECT MAX(crd.cls_course_no) as a  FROM  cls_register_detail crd  " +
                     "left outer join cls_register_mast crm on crm.cls_reg_id = crd.cls_reg_id  " +
                     "where crm.cls_reg_code = '" + p + "  '";
            Conn.Open();
            //MySqlDataReader dtReader;
            //objCmd = new MySqlCommand(strSQL, Conn);
            //dtReader = objCmd.ExecuteReader();
            //if (dtReader.HasRows)
            //{ 
            //    dtReader.
            //}
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);

            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {
                int b = 0;
                //Label4.Text = Convert.ToString(ds4.Tables[0].Rows[i]["family_id"]).ToString();
                if (ds4.Tables[0].Rows[i]["a"]!= DBNull.Value)
                {
                    b = Convert.ToInt32(ds4.Tables[0].Rows[i]["a"]);
                    clsCourseNo.Text = Convert.ToString(b + 1);
                    
                }
                else 
                {
                    
                    clsCourseNo.Text = Convert.ToString(b + 1);
                }
                
                //clsCourseNo.Text = Convert.ToString( b + 1);        
                //TextBox5.Text = (string)ds4.Tables[0].Rows[i]["family_name"].ToString();

            }


           
        }


        void Editing()
        {
            CMD_INSERT.Enabled = false;
            CMD_EDIT.Enabled = true;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;




            strSQL = "select crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time,crd.cls_course_amt," +
                          "crm.cls_reg_code,crm.cls_name,crm.level " +
                          "from cls_register_detail crd left outer join cls_register_mast crm  " +
                          "on crd.cls_reg_id = crm.cls_reg_id  " +
                          "WHERE crd.cls_reg_detail_id ='" + clsRegDetailId.Text + "'";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);

            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                clsRegDetailId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["cls_reg_detail_id"]).ToString();
                clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
                clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
                clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                clsCourseAmt.Text = (string)ds4.Tables[0].Rows[i]["cls_course_amt"].ToString();
                clsRegId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_id"].ToString();
                clsCourseNo.Text = (string)ds4.Tables[0].Rows[i]["cls_course_no"].ToString();

            }
        }
        
        protected void CMD_INSERT_Click(object sender, EventArgs e)
        {
            using (ES_Project.Model.essystemEntities context = new ES_Project.Model.essystemEntities())
            {
                Button bt = (Button)sender;
                switch (bt.ID)
                {
                    case "CMD_INSERT":
                        cls_register_detail ecm = new cls_register_detail();
                        if (clsRegId.Text != null && clsRegId.Text != "")
                        {
                            ecm.cls_reg_id = Convert.ToInt32(clsRegId.Text);
                        }
                        ecm.cls_course_time = clsCourseTime.Text;
                        ecm.cls_course_amt = clsCourseAmt.Text;
                        ecm.cls_course_no = clsCourseNo.Text;
                        ecm.create_by = "Tong";
                        ecm.create_date = DateTime.Now;

                        context.cls_register_detail.AddObject(ecm);
                        // context.SaveChanges();
                        if (context.SaveChanges() > 0)
                        {
                            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert1", "alert('บันทึกเรียบร้อยแล้ว')", true);
                            BindData();
                            //GridView4.DataBind();
                           
                            //GridView4.SelectedIndex = -1;
                            clsRegCode.Text = "";
                            clsName.Text = "";
                            clsRegId.Text = "";
                            clsCourseTime.Text = "";
                            clsCourseAmt.Text = "";
                            clsCourseNo.Text = "";
                        }

                        break;
                    case "CMD_EDIT":
                         //// string StrConn = WebConfigurationManager.ConnectionStrings["essystem"].ToString();
                         // MySqlConnection Conn = new MySqlConnection();
                         //// MySqlCommand objCmd = new MySqlCommand();
                          
                         // //objConn = new MySqlConnection(StrConn);
                         // //objConn.Open();
                         // string strSQL;
                         // strSQL = "UPDATE cls_register_detail ss SET " +
                         // " ss.cls_course_time = '" + this.clsCourseTime.Text + "' " +
                         // " ,ss.cls_course_amt = '" + this.clsCourseAmt.Text + "' " +
                         // " ,ss.cls_reg_id = '" + Convert.ToInt32(clsRegId.Text) + "' "  +
                         // " WHERE ss.cls_reg_detail_id = '" + Convert.ToInt32(clsRegDetailId.Text) + "' ";
                          
                         //MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
                         ////objCmd = new MySqlCommand();
                         //objCmd.Connection = Conn;
                         //objCmd.CommandText = strSQL;
                         //objCmd.CommandType = CommandType.Text;

                         //objCmd.ExecuteNonQuery();

           
                         //BindData();
                         //break;
                        List<cls_register_detail> crm2 = context.cls_register_detail.Where("it.[cls_reg_detail_id]=" + clsRegDetailId.Text + " ").ToList();
                        if (crm2.Count() == 1)
                        {
                            cls_register_detail crmEdit = context.cls_register_detail.Where("it.[cls_reg_detail_id]=" + clsRegDetailId.Text + " ").First();
                            if (crmEdit != null)
                            {
                                crmEdit.cls_course_time = clsCourseTime.Text;
                                crmEdit.cls_course_amt = clsCourseAmt.Text;
                                crmEdit.cls_reg_id = Convert.ToInt32(clsRegId.Text);
                                crmEdit.cls_course_no = clsCourseNo.Text;
                                crmEdit.update_by = "Tong";
                                crmEdit.update_date = DateTime.Now;

                                if (context.SaveChanges() > 0)
                                {

                                    ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert2", "alert('แก้ไขข้อมูลเรียบร้อยแล้ว')", true);
                                    //GridView4.DataBind();
                                    BindData();
                                    GridView4.SelectedIndex = -1;
                                    
                                    clsRegCode.Text = "";
                                    clsName.Text = "";
                                    clsRegId.Text = "";
                                    clsCourseTime.Text = "";
                                    clsCourseAmt.Text = "";
                                    clsCourseNo.Text = "";
                                    CMD_INSERT.Enabled = true;
                                    CMD_EDIT.Enabled = false;
                                }
                            }
                        }
                        break;
                }
            }
        }
         void BindData()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            String strSQL;
           
             
            
                strSQL = "select crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time,crd.cls_course_amt," +
                          "crm.cls_reg_code,crm.cls_name,crm.level " +
                          "from cls_register_detail crd left outer join cls_register_mast crm  " +
                          "on crd.cls_reg_id = crm.cls_reg_id";



                Conn.Open();
                MySqlDataReader dtReader;
                objCmd = new MySqlCommand(strSQL, Conn);
                dtReader = objCmd.ExecuteReader();

                //*** BindData to GridView ***//
                GridView4.DataSource = dtReader;
                GridView4.DataBind();

               // dtReader.Close();
               // dtReader = null;

            
            //using (essystemEntities context = new essystemEntities())
            //{

            //    List<cls_register_detail> mao = context.cls_register_detail.Where("it.[cls_reg_detail_id]=" + ((HiddenField)GridView4.SelectedRow.Cells[1].FindControl("HiddenField3")).Value.ToString() + "").ToList();
            //    if (mao.Count > 0)
            //    {
            //        List<cls_register_detail> mb = context.cls_register_detail.Where("it.[cls_reg_detail_id]=" + ((HiddenField)GridView4.SelectedRow.Cells[1].FindControl("HiddenField3")).Value.ToString() + "").ToList();
            //        if (mb.Count > 0)
            //        {
            //            clsCourseAmt.Text = mb[0].cls_course_amt;
            //            clsCourseTime.Text = mb[0].cls_course_time;
            //            //empNickname.Text = mb[0].emp_nickname;
            //            //empDrgree.Text = mb[0].emp_gender;
            //            clsRegDetailId.Text = mb[0].cls_reg_detail_id.ToString();
            //            //empDegreeSubject.Text = mb[0].emp_degree_subject;
            //            //empInstitution.Text = mb[0].emp_institution;

            //        }
            //    }

            //}

        }
         void RuningNumber()
         {
             MySqlConnection Conn = new MySqlConnection(StrConn);
             String strSQL;

             strSQL = "select crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time,crd.cls_course_amt," +
                       "crm.cls_reg_code,crm.cls_name,crm.level " +
                       "from cls_register_detail crd left outer join cls_register_mast crm  " +
                       "on crd.cls_reg_id = crm.cls_reg_id";



             Conn.Open();
             MySqlDataReader dtReader;
             objCmd = new MySqlCommand(strSQL, Conn);
             dtReader = objCmd.ExecuteReader();

             //*** BindData to GridView ***//
             GridView4.DataSource = dtReader;
             GridView4.DataBind();

             // dtReader.Close();
             // dtReader = null;


             //using (essystemEntities context = new essystemEntities())
             //{

             //    List<cls_register_detail> mao = context.cls_register_detail.Where("it.[cls_reg_detail_id]=" + ((HiddenField)GridView4.SelectedRow.Cells[1].FindControl("HiddenField3")).Value.ToString() + "").ToList();
             //    if (mao.Count > 0)
             //    {
             //        List<cls_register_detail> mb = context.cls_register_detail.Where("it.[cls_reg_detail_id]=" + ((HiddenField)GridView4.SelectedRow.Cells[1].FindControl("HiddenField3")).Value.ToString() + "").ToList();
             //        if (mb.Count > 0)
             //        {
             //            clsCourseAmt.Text = mb[0].cls_course_amt;
             //            clsCourseTime.Text = mb[0].cls_course_time;
             //            //empNickname.Text = mb[0].emp_nickname;
             //            //empDrgree.Text = mb[0].emp_gender;
             //            clsRegDetailId.Text = mb[0].cls_reg_detail_id.ToString();
             //            //empDegreeSubject.Text = mb[0].emp_degree_subject;
             //            //empInstitution.Text = mb[0].emp_institution;

             //        }
             //    }

             //}

         }
        protected void GridView4_RowDataBound(object sender, GridViewRowEventArgs e)
        {
           
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                // loop all data rows
                foreach (DataControlFieldCell cell in e.Row.Cells)
                {
                    // check all cells in one row
                    foreach (Control control in cell.Controls)
                    {
                        // Must use LinkButton here instead of ImageButton
                        // if you are having Links (not images) as the command button.
                        ImageButton button = control as ImageButton;
                        if (button != null && button.CommandName == "Delete")
                            // Add delete confirmation
                            button.OnClientClick = "if (!confirm('คุณต้องการที่จะลบข้อมูล?')) return;";
                    }
                
            }
        }
        }
        protected void GridView4_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
        {

            CMD_INSERT.Enabled = false;
            CMD_EDIT.Enabled = true;
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;




            strSQL = "select crd.cls_reg_detail_id,crd.cls_reg_id,crd.cls_course_no,crd.cls_course_time,crd.cls_course_amt," +
                          "crm.cls_reg_code,crm.cls_name,crm.level " +
                          "from cls_register_detail crd left outer join cls_register_mast crm  " +
                          "on crd.cls_reg_id = crm.cls_reg_id  " +
                          "WHERE crd.cls_reg_detail_id ='" + GridView4.DataKeys[e.NewSelectedIndex].Value + "'";

            Conn.Open();
            MySqlDataAdapter da4 = new MySqlDataAdapter(strSQL, Conn);
            DataSet ds4 = new DataSet();
            da4.Fill(ds4);

            int _row = ds4.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                clsRegDetailId.Text = Convert.ToString(ds4.Tables[0].Rows[i]["cls_reg_detail_id"]).ToString();
                clsRegCode.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_code"].ToString();
                clsName.Text = (string)ds4.Tables[0].Rows[i]["cls_name"].ToString();
                clsCourseTime.Text = (string)ds4.Tables[0].Rows[i]["cls_course_time"].ToString();
                clsCourseAmt.Text = (string)ds4.Tables[0].Rows[i]["cls_course_amt"].ToString();
                clsRegId.Text = (string)ds4.Tables[0].Rows[i]["cls_reg_id"].ToString();
                clsCourseNo.Text = (string)ds4.Tables[0].Rows[i]["cls_course_no"].ToString();
 
            }
        }
        protected void GridView4_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);
            string strSQL;
            strSQL = "DELETE FROM cls_register_detail WHERE cls_reg_detail_id ='" + GridView4.DataKeys[e.RowIndex].Value + "'";
            Conn.Open();
            objCmd = new MySqlCommand(strSQL, Conn);

            objCmd.ExecuteNonQuery();


            ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Alert3", "alert('ลบข้อมูลเรียบร้อยแล้ว')", true);
            GridView4.EditIndex = -1;

            BindData();

        }
        protected void CMD_CLEAR_Click(object sender, EventArgs e)
        {
            clear();
        }
        void clear ()
       { 
            clsRegCode.Text = "";
            clsName.Text = "";
            clsCourseAmt.Text = "";
            clsCourseNo.Text = "";
            clsCourseTime.Text = "";
            CMD_INSERT.Enabled = true;
            CMD_EDIT.Enabled = false;
        
        }

        

    }
}